﻿<%@ WebHandler Language="C#" Class="BusinessReportBide" %>

using System;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data;
using Chenxin.Lib.Utility;
using Chenxin.Lib.Business;
using Chenxin.Lib.Business.BLL;

public class BusinessReportBide : AjaxHandlerBase
{

    public BusinessReportBide()
    {
        //导出
        htTypePermit.Add("export", Constants.Permit_BusinessReport_Export);
    }
    
    override public void RequestProcess(HttpContext context)
    {
        try
        {
            context.Response.ContentType = "text/plain";
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";

            string strOp = context.Request["op"];
            string strRet = string.Empty;
            switch (strOp.ToLower())
            {
                //添加报备相关信息
                case "getlist":
                    GetList(context);
                    break;
                //明细
                case "getdetails":
                    GetDetails(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Constants.ExecResult.Error, ex.Message, ""));
        }
    }

    private void GetDetails(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        //查询5级
        Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        sb.Append(" [Status]=1 ");
        if (company != Guid.Empty)
        {
            sb.AppendFormat(" and BargainCompanyId='{0}' ", company);
        }
        else if (city != Guid.Empty)
        {
            sb.AppendFormat(" and BargainCityId='{0}'  ", city);
        }
        else
        {
            sb.AppendFormat(" and BargainCompanyId='{0}' ", CurrentUserInfo.CompanyId);
        }

        //区域
        if (!string.IsNullOrEmpty(context.Request["region"]))
        {
            sb.AppendFormat(" and Region='{0}' ", context.Request["region"]);
        }

        //时间类型
        string datetype = context.Request["datetype"];
        if (!string.IsNullOrEmpty(context.Request["stime"]))
        {
            sb.AppendFormat(" and {0}>='{1}' ", datetype, context.Request["stime"]);
        }
        if (!string.IsNullOrEmpty(context.Request["etime"]))
        {
            sb.AppendFormat(" and {0}<'{1}' ", datetype, DateTime.Parse(context.Request["etime"]).AddDays(1));
        }
        if (!string.IsNullOrEmpty(context.Request["btype"]))
        {
            switch (context.Request["btype"].ToString())
            {
                case "10":  //居住用房
                    sb.Append(" and HouseProperty=0 ");
                    break;
                case "20":  //非居住用房
                    sb.Append(" and HouseProperty=100 ");
                    break;
            }
        }
        if (!string.IsNullOrEmpty(context.Request["ctype"]))
        {
            switch (context.Request["ctype"].ToString())
            {
                case "10":  //买卖
                    sb.Append(" and BusinessType=10 ");
                    break;
                case "20":  //租赁
                    sb.Append(" and BusinessType=20 ");
                    break;
                case "30":  //合计
                    sb.Append(" and (BusinessType=10 or BusinessType=20) ");
                    break;
            }
        }

        //页码
        int strPageIndex = (string.IsNullOrEmpty(context.Request["pageindex"])) ? 1 : int.Parse(context.Request["pageindex"]) + 1;//页码
        //页显示行数
        int strPageSize = (string.IsNullOrEmpty(context.Request["pagesize"])) ? 30 : int.Parse(context.Request["pagesize"]);//页记录数
        string FieldList = "ContractCode,HouseCode,HouseAddress,Area,BargainPrice,RealCommision";
        string TableFieldList = "ContractCode,HouseCode,HouseAddress,Area,BargainPrice,isnull(VRealCommision + BRealCommision,0) as RealCommision";

        DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex, strPageSize, "HouseUse", "t_Contract_BaseInfo", sb.ToString(), FieldList, TableFieldList, true);

        //*************获取总合计数*****************
        string str = "select sum(Area) as Area,sum(BargainPrice) as BargainPrice,sum(VRealCommision + BRealCommision) as RealCommision  from t_Contract_BaseInfo where " + sb.ToString();
        DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(str);
        System.Data.DataRow dr = ds.Tables[0].NewRow();
        dr["ContractCode"] = "总合计";
        dr["Area"] = ds_count.Tables[0].Rows[0]["Area"];
        dr["BargainPrice"] = ds_count.Tables[0].Rows[0]["BargainPrice"];
        dr["RealCommision"] = ds_count.Tables[0].Rows[0]["RealCommision"];
        ds.Tables[0].Rows.Add(dr);
        //*****************************************

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[1].Rows[0][0], Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0])));
    }
    
    
    private void GetList(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        //查询5级
        Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        sb.Append(" and [Status]=1 ");
        if (company != Guid.Empty)
        {
            sb.AppendFormat(" and BargainCompanyId='{0}' ", company);
        }
        else if (city != Guid.Empty)
        {
            sb.AppendFormat(" and BargainCityId='{0}'  ", city);
        }
        else
        {
            sb.AppendFormat(" and BargainCompanyId='{0}' ", CurrentUserInfo.CompanyId);
        }
        
        //区域
        if (!string.IsNullOrEmpty(context.Request["region"]))
        {
            sb.AppendFormat(" and Region='{0}' ", context.Request["region"]);
        }
        
        //时间类型
        string datetype = context.Request["datetype"];
        if (!string.IsNullOrEmpty(context.Request["stime"]))
        {
            sb.AppendFormat(" and {0}>='{1}' ", datetype, context.Request["stime"]);
        }
        if (!string.IsNullOrEmpty(context.Request["etime"]))
        {
            sb.AppendFormat(" and {0}<'{1}' ", datetype, DateTime.Parse(context.Request["etime"]).AddDays(1));
        }

        StringBuilder strSql = new StringBuilder();
        strSql.Append("");
        
        strSql.Append("select '套数' as Indicators,");
        strSql.Append("sum(case BusinessType when 10 then 1 else 0 end) as s_cout,");
        strSql.Append("sum(case BusinessType when 20 then 1 else 0 end) as r_cout,");
        strSql.Append("case HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end as c_type");
        strSql.Append(" from dbo.t_Contract_BaseInfo ");
        strSql.AppendFormat("where (BusinessType = 10 Or BusinessType = 20) {0} ",sb.ToString());
        strSql.Append(" group by case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end");

        strSql.Append(" union ");

        strSql.Append("select '面积' as Indicators,");
        strSql.Append("sum(case BusinessType when 10 then isnull(Area,0) else 0 end) as s_cout,");
        strSql.Append("sum(case BusinessType when 20 then isnull(Area,0) else 0 end) as r_cout,");
        strSql.Append("case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end as c_type");
        strSql.Append(" from dbo.t_Contract_BaseInfo ");
        strSql.AppendFormat("where (BusinessType = 10 Or BusinessType = 20) {0} ", sb.ToString());
        strSql.Append(" group by case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end");

        strSql.Append(" union ");

        strSql.Append("select '金额' as Indicators,");
        strSql.Append("sum(case BusinessType when 10 then BargainPrice else 0 end) as s_cout,");
        strSql.Append("sum(case BusinessType when 20 then BargainPrice else 0 end) as r_cout,");
        strSql.Append("case HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end as c_type");
        strSql.Append(" from dbo.t_Contract_BaseInfo ");
        strSql.AppendFormat("where (BusinessType = 10 Or BusinessType = 20) {0} ", sb.ToString());
        strSql.Append(" group by case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end");

        strSql.Append(" union ");
        strSql.Append(" select '营业收入' as Indicators,");
        strSql.Append("sum(case BusinessType when 10 then (VRealCommision + BRealCommision) else 0 end) as s_cout,");
        strSql.Append("sum(case BusinessType when 20 then (VRealCommision + BRealCommision) else 0 end) as r_cout,");
        strSql.Append("case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end as c_type");
        strSql.Append(" from dbo.t_Contract_BaseInfo ");
        strSql.AppendFormat("where (BusinessType = 10 Or BusinessType = 20) {0} ", sb.ToString());
        strSql.Append(" group by case  HouseProperty when 0 then '居住用房' when 100 then '非居住用房' end");

        strSql.Append(" order by  Indicators,c_type");
        
        DataSet ds = Chenxin.Lib.Business.DAL.t_Personal_FinancePayedSumInfo.ExecSql(strSql.ToString());

        string strHTML = GetHTML(ds.Tables[0]);

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{html:'{0}'}}}}", strHTML));
    }

    private string GetHTML(DataTable dt)
    {
         StringBuilder sb = new StringBuilder();

         sb.Append("<table style=\"width:760px;text-align: center;\" border=\"1\" ><tr class=\"x-grid-head\">");
         sb.Append("<tr class=\"x-grid-head\"><td style=\"width:300px;text-align: center;\" colspan=\"2\">指标/业务</td>");
         sb.Append("<td style=\"width:150px;text-align: center;\">买卖</td>");
         sb.Append("<td style=\"width:150px;text-align: center;\">租赁</td>");
         sb.Append("<td style=\"width:150px;text-align: center;\">合计</td>");
         sb.Append("</tr><tr style=\"text-align: center;\" class=\"x-grid-row\">");
         sb.Append("<td rowspan=\"3\" style=\"text-align: center;\">套数</td>");
         sb.AppendFormat("<td style=\"text-align: center;\">居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "套数", 1, "居住用房"), GetTotal(dt, "套数", 2, "居住用房"), GetTotal(dt, "套数", 3, "居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;\" class=\"x-grid-row\"><td style=\"text-align: center;\">非居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "套数", 1, "非居住用房"), GetTotal(dt, "套数", 2, "非居住用房"), GetTotal(dt, "套数", 3, "非居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;background-color:#E8FAFD;\" class=\"x-grid-row\"><td style=\"text-align: center;\">小计</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "套数", 1, ""), GetTotal(dt, "套数", 2, ""), GetTotal(dt, "套数", 3, ""));
         sb.Append("</tr><tr style=\"text-align: center;\" class=\"x-grid-row\">");
         sb.Append("<td rowspan=\"3\" style=\"text-align: center;\">面积</td>");
         sb.AppendFormat("<td style=\"text-align: center;\">居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "面积", 1, "居住用房"), GetTotal(dt, "面积", 2, "居住用房"), GetTotal(dt, "面积", 3, "居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;\" class=\"x-grid-row\"><td style=\"text-align: center;\">非居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "面积", 1, "非居住用房"), GetTotal(dt, "面积", 2, "非居住用房"), GetTotal(dt, "面积", 3, "非居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;background-color:#E8FAFD;\" class=\"x-grid-row\"><td style=\"text-align: center;\">小计</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "面积", 1, ""), GetTotal(dt, "面积", 2, ""), GetTotal(dt, "面积", 3, ""));
         sb.Append("</tr><tr style=\"text-align: center;\" class=\"x-grid-row\">");
         sb.Append("<td rowspan=\"3\" style=\"text-align: center;\">成交金额</td>");
         sb.AppendFormat("<td style=\"text-align: center;\">居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "金额", 1, "居住用房"), GetTotal(dt, "金额", 2, "居住用房"), GetTotal(dt, "金额", 3, "居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;\" class=\"x-grid-row\"><td style=\"text-align: center;\">非居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "金额", 1, "非居住用房"), GetTotal(dt, "金额", 2, "非居住用房"), GetTotal(dt, "金额", 3, "非居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;background-color:#E8FAFD;\" class=\"x-grid-row\"><td style=\"text-align: center;\">小计</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "金额", 1, ""), GetTotal(dt, "金额", 2, ""), GetTotal(dt, "金额", 3, ""));
         sb.Append("</tr><tr style=\"text-align: center;\" class=\"x-grid-row\">");
         sb.Append("<td rowspan=\"3\" style=\"text-align: center;\">营业收入</td>");
         sb.AppendFormat("<td style=\"text-align: center;\">居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"10\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "营业收入", 1, "居住用房"), GetTotal(dt, "营业收入", 2, "居住用房"), GetTotal(dt, "营业收入", 3, "居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;\" class=\"x-grid-row\"><td style=\"text-align: center;\">非居住用房</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"20\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "营业收入", 1, "非居住用房"), GetTotal(dt, "营业收入", 2, "非居住用房"), GetTotal(dt, "营业收入", 3, "非居住用房"));
         sb.AppendFormat("<tr style=\"text-align: center;background-color:#E8FAFD;\" class=\"x-grid-row\"><td style=\"text-align: center;\">小计</td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"10\">{0:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"20\">{1:f2}</a></td><td style=\"text-align: right;\"><a class=\"a_details\" style=\"cursor:pointer\" btype=\"30\" ctype=\"30\">{2:f2}</a></td></tr>", GetTotal(dt, "营业收入", 1, ""), GetTotal(dt, "营业收入", 2, ""), GetTotal(dt, "营业收入", 3, ""));
         sb.Append("</tr></table>");
        
         return sb.ToString();
    }

    private decimal GetTotal(DataTable dt,string type, int i,string businesstype)
    {
        decimal total = 0;
        string strsql = "";
        if (!string.IsNullOrEmpty(type) && !string.IsNullOrEmpty(businesstype))
            strsql = "Indicators='" + type + "' and c_type='" + businesstype + "'";
        else
            strsql = "Indicators='" + type + "'";
        DataRow[] list = dt.Select(strsql);
        if (list.Length > 0)
        {
            foreach (DataRow dr in list)
            {
                if (i == 1)
                    total += (decimal)dr["s_cout"];
                else if (i == 2)
                    total += (decimal)dr["r_cout"];
                else
                {
                    total += (decimal)dr["s_cout"] + (decimal)dr["r_cout"];
                }
            }
        }        
        return total;
    }
}